from datetime import datetime

from db_conn import pd_conn_zfw
import pandas as pd


def day_time_order_count(path, startTime, endTime):
    dates = pd.date_range(startTime, pd.to_datetime(endTime) - pd.Timedelta(days=1), freq='D').tolist()

    df_month = pd.DataFrame([], columns=['f_factory_id', 'cc'])

    for dt in dates:
        date = dt.strftime('%Y-%m-%d')
        sql = """
        select f_factory_id,count(1)
        from t_order 
        where f_order_date>=concat('{date}',' 08:00:00')
        and f_order_date<concat('{date}',' 21:00:00')
        and f_status in (50,60)
        group by f_factory_id
        """.format(date=date)
        df_day = pd_conn_zfw(sql)
        df_month = pd.concat([df_month, df_day]).sort_values('f_factory_id')
    df_group = df_month.groupby('f_factory_id').sum()

    title_time1 = startTime[-5:-3] + startTime[-2:]
    title_time2 = endTime[-5:-3] + endTime[-2:]
    title_time3 = datetime.strftime(datetime.now(), '%M%S')

    df_group.to_excel(r'{}\8-21洗车单量{}~{}({}).xlsx'.format(path, title_time1, title_time2, title_time3))
    print('DOWN!')
